<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>CREATE SCHEMA</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.2 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="CREATE RULE"
HREF="sql-createrule.html"><LINK
REL="NEXT"
TITLE="CREATE SEQUENCE"
HREF="sql-createsequence.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2011-12-01T22:07:59"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.2 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="CREATE RULE"
HREF="sql-createrule.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-commands.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="CREATE SEQUENCE"
HREF="sql-createsequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SQL-CREATESCHEMA"
></A
>CREATE SCHEMA</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN66974"
></A
><H2
>Name</H2
>CREATE SCHEMA&nbsp;--&nbsp;define a new schema</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN66979"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>CREATE SCHEMA <TT
CLASS="REPLACEABLE"
><I
>schema_name</I
></TT
> [ AUTHORIZATION <TT
CLASS="REPLACEABLE"
><I
>user_name</I
></TT
> ] [ <TT
CLASS="REPLACEABLE"
><I
>schema_element</I
></TT
> [ ... ] ]
CREATE SCHEMA AUTHORIZATION <TT
CLASS="REPLACEABLE"
><I
>user_name</I
></TT
> [ <TT
CLASS="REPLACEABLE"
><I
>schema_element</I
></TT
> [ ... ] ]</PRE
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN66986"
></A
><H2
>Description</H2
><P
>   <TT
CLASS="COMMAND"
>CREATE SCHEMA</TT
> enters a new schema
   into the current database.
   The schema name must be distinct from the name of any existing schema
   in the current database.
  </P
><P
>   A schema is essentially a namespace:
   it contains named objects (tables, data types, functions, and operators)
   whose names can duplicate those of other objects existing in other
   schemas.  Named objects are accessed either by <SPAN
CLASS="QUOTE"
>"qualifying"</SPAN
>
   their names with the schema name as a prefix, or by setting a search
   path that includes the desired schema(s).  A <TT
CLASS="LITERAL"
>CREATE</TT
> command
   specifying an unqualified object name creates the object
   in the current schema (the one at the front of the search path,
   which can be determined with the function <CODE
CLASS="FUNCTION"
>current_schema</CODE
>).
  </P
><P
>   Optionally, <TT
CLASS="COMMAND"
>CREATE SCHEMA</TT
> can include subcommands
   to create objects within the new schema.  The subcommands are treated
   essentially the same as separate commands issued after creating the
   schema, except that if the <TT
CLASS="LITERAL"
>AUTHORIZATION</TT
> clause is used,
   all the created objects will be owned by that user.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN66997"
></A
><H2
>Parameters</H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>schema_name</I
></TT
></DT
><DD
><P
>        The name of a schema to be created.  If this is omitted, the user name
        is used as the schema name.  The name cannot
        begin with <TT
CLASS="LITERAL"
>pg_</TT
>, as such names
        are reserved for system schemas.
       </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>user_name</I
></TT
></DT
><DD
><P
>        The name of the user who will own the schema.  If omitted,
        defaults to the user executing the command.  Only superusers
        can create schemas owned by users other than themselves.
       </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>schema_element</I
></TT
></DT
><DD
><P
>        An SQL statement defining an object to be created within the
        schema. Currently, only <TT
CLASS="COMMAND"
>CREATE
        TABLE</TT
>, <TT
CLASS="COMMAND"
>CREATE VIEW</TT
>, <TT
CLASS="COMMAND"
>CREATE
        INDEX</TT
>, <TT
CLASS="COMMAND"
>CREATE SEQUENCE</TT
>, <TT
CLASS="COMMAND"
>CREATE
        TRIGGER</TT
> and <TT
CLASS="COMMAND"
>GRANT</TT
> are accepted as clauses
        within <TT
CLASS="COMMAND"
>CREATE SCHEMA</TT
>. Other kinds of objects may
        be created in separate commands after the schema is created.
       </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN67023"
></A
><H2
>Notes</H2
><P
>   To create a schema, the invoking user must have the
   <TT
CLASS="LITERAL"
>CREATE</TT
> privilege for the current database.
   (Of course, superusers bypass this check.)
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN67027"
></A
><H2
>Examples</H2
><P
>   Create a schema:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE SCHEMA myschema;</PRE
><P>
  </P
><P
>   Create a schema for user <TT
CLASS="LITERAL"
>joe</TT
>; the schema will also be
   named <TT
CLASS="LITERAL"
>joe</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE SCHEMA AUTHORIZATION joe;</PRE
><P>
  </P
><P
>   Create a schema and create a table and view within it:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE SCHEMA hollywood
    CREATE TABLE films (title text, release date, awards text[])
    CREATE VIEW winners AS
        SELECT title, release FROM films WHERE awards IS NOT NULL;</PRE
><P>
   Notice that the individual subcommands do not end with semicolons.
  </P
><P
>   The following is an equivalent way of accomplishing the same result:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE VIEW hollywood.winners AS
    SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;</PRE
><P></P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN67039"
></A
><H2
>Compatibility</H2
><P
>   The SQL standard allows a <TT
CLASS="LITERAL"
>DEFAULT CHARACTER SET</TT
> clause
   in <TT
CLASS="COMMAND"
>CREATE SCHEMA</TT
>, as well as more subcommand
   types than are presently accepted by
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>.
  </P
><P
>   The SQL standard specifies that the subcommands in <TT
CLASS="COMMAND"
>CREATE
   SCHEMA</TT
> can appear in any order.  The present
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> implementation does not
   handle all cases of forward references in subcommands; it might
   sometimes be necessary to reorder the subcommands in order to avoid
   forward references.
  </P
><P
>   According to the SQL standard, the owner of a schema always owns
   all objects within it.  <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
   allows schemas to contain objects owned by users other than the
   schema owner.  This can happen only if the schema owner grants the
   <TT
CLASS="LITERAL"
>CREATE</TT
> privilege on his schema to someone else.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN67051"
></A
><H2
>See Also</H2
><A
HREF="sql-alterschema.html"
>ALTER SCHEMA</A
>, <A
HREF="sql-dropschema.html"
>DROP SCHEMA</A
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createrule.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-createsequence.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE RULE</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>CREATE SEQUENCE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>